/*
 * @Description: SQLite查询封装（中间件）
 * @Autor: HuiSir<273250950@qq.com>
 * @Date: 2021-05-26 17:53:39
 * @LastEditTime: 2022-07-26 17:10:04
 */

import { print } from '../hooks/useLog.js'
import { Pool } from '../hooks/useMysqlPool.js'
import Genid from './Genid.js'

interface IWhereItem<T> {
    [key: string]: T
}

interface IWhere extends IWhereItem<IWhereItem<any>> {
    _OR?: IWhereItem<any>
    _AND?: IWhereItem<any>
}

interface IListParams {
    filter?: string
    sort?: string
    page?: number
    limit?: number
    fuzzy?: boolean //模糊查询
}

/**
 * 类型：
 * INT-整型，最长11位，范围(0，4294967295)
 * BIGINT-长整型，64位，范围(0，18446744073709551615)，可用于存储时间戳
 * TINYINT-一个字节，8位,一般用于布尔值0和1或状态,范围无符号(0，255)
 * SMALLINT-2个字节，16位，范围(0，65535)
 * VARCHAR-可变长字符串，最长255位
 * CHAR-定长字符串，最长255位（分配固定空间，速度快，但可能会早餐内存浪费）
 * DATETIME- 8字节时间，时间范围是 1001 年到 9999 年，精度为秒，它把日期和时间封装到格式为 YYYYMMDDHHMMSS 的整数
 */
interface ISchemaItem {
    type: 'INT' | 'BIGINT' | 'SMALLINT' | 'TINYINT' | 'FLOAT' | 'DOUBLE' |
    'VARCHAR(12)' | 'VARCHAR(36)' | 'VARCHAR(72)' | 'VARCHAR(120)' | 'VARCHAR(255)' |
    'CHAR(12)' | 'CHAR(36)' | 'CHAR(72)' | 'CHAR(120)' | 'CHAR(255)' | 'BLOB' | 'TEXT' |
    'BIT' | 'DATE' | 'DATETIME' | 'YEAR' | 'TIME' | 'TIMESTAMP' | 'SET' | 'ENUM'
    // CURRENT_TIMESTAMP-当前时间，type为DATETIME时可配置
    // 若默认值为字符串表达式时注意带单引号
    // 不能为BLOB和TEXT列分配默认值
    default?: string | number
    notNull?: boolean // 非空
    comment?: string // 注释
}

/**
 * 字段习惯用下划线，不用驼峰
 * 若字符串不确定长度，且不是很长，一般设为VARCHAR(255)
 * 若默认值为字符串表达式时注意带单引号
 */
export type ISchema = IWhereItem<ISchemaItem>

export default class SQLAgent {
    /**
     * SQL值转换
     * string|boolean|number|null|undefined
     * undefined或者对象、数组会被直接忽略
     */
    static valueConvert(val: any) {
        if (val === null) {
            return 'NULL'
        }
        if (typeof val === 'boolean') {
            return val ? 'TRUE' : 'FALSE'
        }
        if (typeof val === 'number' || typeof val === 'bigint') {
            return `${val}`
        }
        if (typeof val === 'string') {
            return `'${val}'`
        }
        return undefined
    }

    /**
     * 返回字段过滤
     */
    static fieldFilter(filter: string, schema: ISchema) {
        const filterKeys = filter.split(' ')
        const removeKeys: string[] = []
        const getOutKeys: string[] = []
        filterKeys.forEach((item) => {
            if (item[0] === '-') {
                removeKeys.push(item.slice(1))
            } else {
                getOutKeys.push(item)
            }
        })
        return (
            Object.keys(schema)
                .filter((k) => {
                    return getOutKeys.length > 0
                        ? getOutKeys.includes(k)
                        : !removeKeys.includes(k)
                })
                .join(',') || '*'
        )
    }

    /**
     * 多条件查询条件对象转义
     * @param where  String/Object
     * 注：where为Object类型时仅支持单个AND子句或OR子句，不支持混合子句，支持相等`=`条件和模糊查询`LIKE`
     * 注：where为Object类型时,若where某个字段为数组，则该字段数组中的值为OR子句，与其他字段用AND连接
     * fuzzy=true时为模糊查询
     */
    static obj2whereStr(where: IWhere | string, fuzzy = false): string {
        //字符串直接返回
        if (typeof where === 'string') {
            return where
        }
        //对象类型
        const whereStrArr = []
        let joinStr = ' AND '
        const operatChar = fuzzy ? ' LIKE ' : ' = ' //运算符
        if (where.hasOwnProperty('_OR')) {
            Object.keys(where._OR!).forEach((key) => {
                const sqlVal = SQLAgent.valueConvert(where._OR![key])
                const val = fuzzy ? `%${sqlVal}%` : sqlVal
                val && whereStrArr.push(`${key}${operatChar}${val}`)
            })
            joinStr = ' OR '
        } else {
            const curWhere = where.hasOwnProperty('_AND') ? where._AND : where
            for (const key in curWhere) {
                if (curWhere[key] instanceof Array) {
                    const tempArr = curWhere[key].map(
                        (item: string) => {
                            const sqlVal = SQLAgent.valueConvert(item)
                            const val = fuzzy ? `%${sqlVal}%` : sqlVal
                            return val ? `${key}${operatChar}${val}` : false
                        }
                    ).filter((item: any) => item)
                    const tempStr = tempArr.join(' OR ')
                    whereStrArr.push(tempStr)
                } else {
                    const sqlVal = SQLAgent.valueConvert(curWhere[key])
                    const val = fuzzy ? `%${sqlVal}%` : sqlVal
                    val && whereStrArr.push(`${key}${operatChar}${val}`)
                }
            }
        }
        return whereStrArr.join(joinStr)
    }

    /**
     * @description: 将order by传值类似与`id age -update_time`形式的字符串转为query串
     * @param {*} orderStr
     * @return {*}
     * @author: HuiSir
     */
    static sort2QueryStr(orderStr: string): string {
        if (!orderStr || orderStr.trim() === '') {
            return ''
        }

        // 随机
        if (orderStr === 'random') {
            return 'ORDER BY random()'
        }

        const orderArr = orderStr.split(' ')
        const orderArr2 = orderArr.map((item) => {
            //升降序，带-号为降序
            let sortMark = 'ASC' //升序
            if (item[0] === '-') {
                item = item.slice(1)
                sortMark = 'DESC'
            }
            return `${item} ${sortMark}`
        })

        return 'ORDER BY ' + orderArr2.join(',')
    }

    private pool: Pool
    private tableName: string
    private schema: ISchema
    private genid: Genid

    /**
     * 构造方法
     */
    constructor(pool: Pool, tableName: string, schema: ISchema, WorkerId: number = 1) {
        this.pool = pool
        this.tableName = tableName

        // 这里排除可能有的无需字段
        schema['id'] && delete schema['id']
        schema['create_time'] && delete schema['create_time']
        schema['update_time'] && delete schema['update_time']

        // 添加主键id
        this.schema = {
            id: {
                type: 'BIGINT',
                notNull: true,
            },
            ...schema,
        }

        // 这里确保不同的表配置不同的机器码，充分避免id重复问题
        this.genid = new Genid({ WorkerId })

        //数据表初始化创建
        this.dataTableInit()
    }

    /**
     * 数据表初始化
     */
    private async dataTableInit(): Promise<void> {
        const { tableName, schema } = this

        // 判断表是否存在
        const tableExistSql = `SHOW TABLES LIKE '${tableName}'`
        const Conn = await this.pool.getConn()
        Conn.query(tableExistSql, (err, { length }: any) => {
            if (err) {
                Conn.release()
                print.error(`数据表 ${tableName} 初始化失败，请尝试重启：` + err.toString())
            } else {
                //表不存在创建表
                if (length == 0) {
                    let dbQueryStemp = ''
                    const schemaKeys = Object.keys(schema)
                    schemaKeys.forEach((key, index) => {
                        const item = schema[key]
                        // 注釋
                        const COMMENT = item.comment ? ` COMMENT '${item.comment}'` : '';
                        // 列
                        dbQueryStemp += `\`${key}\` ${item.type}${key === 'id' ? ' PRIMARY KEY' : ''}${item.notNull ? ' NOT NULL' : ''
                            }${item.default != void (0)
                                // 字符串添加单引号
                                ? ` DEFAULT ${item.type.includes('CHAR') ? ('\'' + item.default + '\'') : item.default}`
                                : ''
                            }${COMMENT},`;
                    })

                    // 时间戳
                    dbQueryStemp += `\`create_time\` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                        \`update_time\` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'`

                    // 拼接
                    const querystr = `CREATE TABLE IF NOT EXISTS ${tableName} (${dbQueryStemp});`

                    Conn.query(querystr, (err2) => {
                        Conn.release()
                        if (err2) {
                            print.error(`数据表 ${tableName} 初始化失败，请尝试重启：` + err2.toString())
                        } else {
                            print.info(`数据表 ${tableName} 初始化成功`)
                        }
                    })
                } else {
                    Conn.release()
                    print.info(`数据表 ${tableName} 初始化成功`)
                }
            }
        })
    }

    /**
     * 自定义执行语句（不支持多条）
     * @param sqlMod  String
     * @returns {Promise<any>}
     */
    run(sqlMod: string): Promise<any> {
        return new Promise(async (resolve, reject) => {
            const Conn = await this.pool.getConn()
            Conn.query(sqlMod, (err: any, res: any) => {
                Conn.release() // 释放连接
                if (err) {
                    reject(err)
                } else {
                    resolve(res)
                }
            })
        })
    }

    /**
     * 单数据查询接口
     * @param tableName
     * @param slot IWhereItem<any>
     * @returns {Promise<any>}
     */
    findOne(slot: IWhereItem<any>, filter = ''): Promise<any> {
        const { tableName, schema } = this
        //返回字段过滤
        const resFields = SQLAgent.fieldFilter(filter, schema)
        //条件转义
        const whereStr = SQLAgent.obj2whereStr(slot)
        return new Promise(async (resolve, reject) => {
            const sqlMod = `SELECT ${resFields} FROM ${tableName} ${whereStr ? 'WHERE ' + whereStr : ''} limit 1;`
            const Conn = await this.pool.getConn()
            Conn.query(sqlMod, (err, row) => {
                Conn.release()
                if (err) {
                    reject(err)
                } else {
                    resolve(row.pop())
                }
            })
        })
    }

    /**
     * 取数据集合
     * @param slot
     * @returns {Promise<any>}
     */
    find(slot: IWhereItem<any> | string, listParams: IListParams = {}): Promise<any> {
        const { tableName, schema } = this
        const { filter = "", sort = "", page = 1, limit, fuzzy = false } = listParams
        const { fieldFilter, obj2whereStr, sort2QueryStr } = SQLAgent
        //返回字段过滤
        const resFields = fieldFilter(filter, schema)
        //条件转义
        const whereStr = obj2whereStr(slot, fuzzy)
        //升降序，带-号为降序
        const sortQueryStr = sort2QueryStr(sort)
        // 限制
        let limitStr: string
        // 不分页查所有
        if (page === -1) {
            limitStr = limit ? `limit ${limit}` : ""
        } else {
            limitStr = limit ? `limit ${(page - 1) * limit},${limit}` : ""
        }
        return new Promise(async (resolve, reject) => {
            const sqlMod = `SELECT ${resFields} FROM ${tableName} ${whereStr ? 'WHERE ' + whereStr : ''} ${sortQueryStr} ${limitStr};`
            const Conn = await this.pool.getConn()
            Conn.query(sqlMod, function (err: any, rows: any) {
                Conn.release()
                if (err) {
                    reject(err)
                } else {
                    resolve(rows)
                }
            })
        })
    }

    /**
     * 单数据创建接口
     * @param tableName
     * @param slot Object
     * @param filter String el:'-password -id' 要返回的字段或要排除的字段，-号为排除
     * @returns {Promise<any>}
     */
    create(slot: any, filter = ''): Promise<any> {
        const { tableName, schema } = this
        // 主键
        slot.id = this.genid.NextId()
        // 返回字段过滤
        const resFields = SQLAgent.fieldFilter(filter, schema)
        return new Promise(async (resolve, reject) => {
            const sqlMod1 = `INSERT INTO ${tableName} SET ?;`
            const sqlMod2 = `SELECT ${resFields} FROM ${tableName} WHERE id = '${slot.id}';`
            const Conn = await this.pool.getConn()

            Conn.query(sqlMod1, slot, (err: any) => {
                if (err) {
                    Conn.release()
                    reject(err)
                    return
                }
                Conn.query(sqlMod2, (err: any, row: any) => {
                    Conn.release()
                    if (err) {
                        reject(err)
                    } else {
                        resolve(row.pop())
                    }
                })
            })
        })
    }

    /**
     * @description: 多数据创建
     * @param {Array} slot [Object,Object,...]
     * @param {String} filter '-id -name'
     * @return {*}
     * @author: HuiSir
     */
    createMany(slot: any[] = [], filter = ''): Promise<any> {
        if (slot.length === 0) {
            return Promise.reject(new Error('插入数据为空'))
        }

        const { tableName, schema } = this
        const ids: string[] = []

        //插入字段
        const insertKeys = Object.keys(slot[0])
        insertKeys.unshift('id') //插入主键id

        //插入数据转义
        const insertValues = slot.map((item) => {
            item.id = this.genid.NextId()
            ids.push(item.id)
            const valuesArr = insertKeys.map((k) => item[k])
            return valuesArr.join("','")
        })
        const insertValuesStr = insertValues.join("'),('").replace(/\\/g, '\\\\')
        //条件转义
        const whereStr = SQLAgent.obj2whereStr({ id: ids })
        // 返回字段过滤
        const resFields = SQLAgent.fieldFilter(filter, schema);
        return new Promise(async (resolve, reject) => {
            const sqlMod1 = `INSERT INTO ${tableName} (${insertKeys.join(',')}) VALUES ('${insertValuesStr}');`
            const sqlMod2 = `SELECT ${resFields} FROM ${tableName} ${whereStr ? 'WHERE ' + whereStr : ''};`

            const Conn = await this.pool.getConn()

            // 串行
            Conn.query(sqlMod1, (err: any) => {
                if (err) {
                    reject(err)
                    return
                }
                // 读取插入数据
                Conn.query(sqlMod2, (err: any, row: any) => {
                    Conn.release()
                    if (err) {
                        reject(err)
                    } else {
                        resolve(row)
                    }
                })
            })
        })
    }

    /**
     * 数据修改接口
     * @param whereSlot IWhereItem<any>
     * @param updateSlot IWhereItem<any>
     * @returns {Promise<any>}
     */
    update(whereSlot: IWhereItem<any>, updateSlot: IWhereItem<any>): Promise<any> {
        const { tableName } = this
        //条件转义
        const whereStr = SQLAgent.obj2whereStr(whereSlot)
        return new Promise(async (resolve, reject) => {
            const sqlMod = `UPDATE ${tableName} SET ? WHERE ${whereStr}`
            const Conn = await this.pool.getConn()
            Conn.query(sqlMod, updateSlot, (err, result) => {
                Conn.release()
                if (err) {
                    reject(err)
                } else {
                    resolve(result)
                }
            })
        })
    }

    /**
     * 硬删除
     * @param {IWhereItem<any>} slot
     * @param {boolean} returnData  可选参数，是否返回删除的数据
     * @param {string} filter  可选参数，返回数据过滤
     * @returns {Promise<any>}
     */
    remove(slot: IWhereItem<any>, returnData: boolean = false, filter: string = ''): Promise<any> {
        const { tableName, schema } = this
        //条件转义
        const whereStr = SQLAgent.obj2whereStr(slot)
        //返回字段过滤
        const resFields = returnData && SQLAgent.fieldFilter(filter, schema)
        return new Promise(async (resolve, reject) => {
            const sqlDelMod = `DELETE FROM ${tableName} WHERE ${whereStr};`

            //返回被删数据
            const sqlMod = `SELECT ${resFields} FROM ${tableName} WHERE ${whereStr};`

            const Conn = await this.pool.getConn()

            if (returnData) {
                // 串行
                Conn.query(sqlMod, function (err: any, row: any) {
                    if (err) {
                        Conn.release()
                        reject(err)
                    } else {
                        Conn.query(sqlDelMod, function (err2: any) {
                            Conn.release()
                            if (err2) {
                                reject(err)
                            } else {
                                resolve(row.pop())
                            }
                        })
                    }
                })
            } else {
                Conn.query(sqlDelMod, function (err, res) {
                    Conn.release()
                    if (err) {
                        reject(err)
                    } else {
                        resolve(res)
                    }
                })
            }
        })
    }

    /**
     * 批量硬删除,仅限一个字段
     * @param key el: id
     * @param values el: "1,2,3" 
     */
    removeMany(key: string, values: string): Promise<any> {
        const { tableName } = this
        return new Promise(async (resolve, reject) => {
            values = values.split(",").join("','")
            const sqlMod = `DELETE FROM ${tableName} WHERE ${key} IN ('${values}')`
            const Conn = await this.pool.getConn()
            Conn.query(sqlMod, function (err, res) {
                Conn.release()
                if (err) {
                    reject(err)
                } else {
                    resolve(res)
                }
            })
        })
    }

    /**
     * 统计
     * @param slot
     * @returns {Promise<any>}
     */
    count(slot: IWhereItem<any> | string = ""): Promise<any> {
        const { tableName } = this
        //条件转义
        const whereStr = SQLAgent.obj2whereStr(slot)
        return new Promise(async (resolve, reject) => {
            const sqlMod = `SELECT COUNT(*) as count FROM ${tableName} ${whereStr ? 'WHERE ' + whereStr : ''}`
            const Conn = await this.pool.getConn()
            Conn.query(sqlMod, (err, res) => {
                Conn.release()
                if (err) {
                    reject(err)
                } else {
                    resolve(res.pop())
                }
            })
        })
    }

    /**
     * @description: 清空表数据
     * @return {Promise<any>}
     * @author: HuiSir
     */
    clearTable(): Promise<any> {
        const { tableName } = this
        return new Promise(async (resolve, reject) => {
            const sqlMod = `DELETE FROM ${tableName};`
            const Conn = await this.pool.getConn()
            Conn.query(sqlMod, (err, res) => {
                Conn.release()
                if (err) {
                    reject(err)
                } else {
                    resolve(res)
                }
            })
        })
    }
}